import datetime
import io

import openpyxl
from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

from header.pm_header import PMHeader
from services.sql_service.mes_service import MesService


class ExcelFileParseService:
    @classmethod
    async def export_data_to_excel(cls, data):
        header, data = data['header'], data['data']
        workbook = openpyxl.Workbook()
        worksheet = workbook.active

        header_name: list = [h['name'] for h in header]
        header_code: list = [h['code'] for h in header]
        worksheet.append(header_name)
        if data:
            for d in data:
                d_list = [d[h] for h in header_code]
                worksheet.append(d_list)
        buffer = io.BytesIO()
        workbook.save(buffer)
        buffer.seek(0)
        workbook.close()

        return buffer

    @classmethod
    async def template_download(cls, header):
        workbook: Workbook = openpyxl.Workbook()
        worksheet: Worksheet = workbook.active
        worksheet.append(header)
        buffer = io.BytesIO()
        workbook.save(buffer)
        buffer.seek(0)
        workbook.close()
        return buffer

    @classmethod
    async def insert_data_to_excel(cls, file):
        filename, file_data = file.get('filename'), file.get('body')
        db_data_list = await cls.xlsx_file_data_parse(file_data)
        for insert_data_tuple in db_data_list:
            await MesService().insert_issue_order(insert_data_tuple)

    @classmethod
    async def xlsx_file_data_parse(cls, file_data):
        # 解析数据
        wb: Workbook = load_workbook(io.BytesIO(file_data))
        sheet: Worksheet = wb.active
        row_num, col_num = sheet.max_row, sheet.max_column
        db_data_list = []
        cur_time_str = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        # 第二行开始是数据
        for row in range(2, row_num + 1):
            data_tuple = tuple(sheet.cell(row, col).value for col in range(1, col_num + 1))
            time_tuple = (cur_time_str,)
            data = data_tuple + time_tuple
            db_data_list.append(data)
        return db_data_list
